'''
Created on 22 Jan 2010

@author: yusuf
'''
import MySQLdb
from pygooglechart import StackedVerticalBarChart

if __name__ == '__main__':
    dbHost = "localhost"
    dbUser = "smsuser"
    dbPass = "smspass"
    dbName = "smsDb"
    db = MySQLdb.connect(host=dbHost, user=dbUser, passwd=dbPass,db=dbName)
    
    # create a cursor
    cursor = db.cursor()
    campaignTxt = "test"
    # execute SQL statement
    hourlyQuery = "SELECT count(*), date_format(m.`timestamp`,'%W %M %Y %H') FROM smsDb.messages m WHERE m.`campaign`='"+campaignTxt+"' group by date_format(m.`timestamp`,'%W %M %Y %H')"
    dailyQuery = "SELECT count(*), date_format(m.`timestamp`,'%W %M %Y') FROM smsDb.messages m WHERE m.`campaign`='"+campaignTxt+"' AND m.`timestamp` between '2010-01-01' and '2010-02-01' group by date_format(m.`timestamp`,'%W %M %Y')"
  
    cursor.execute(dailyQuery)
    
    # get the resultset as a tuple
    results = cursor.fetchall()
    print results
    
    chart = StackedVerticalBarChart(250, 100,y_range=(0, 35))
    chart.set_bar_width(10)
    chart.set_colours(['00ff00', 'ff0000'])
    dataset = 0
    for result in results:
        dataset = [dataset,result[0]] 
    print dataset
    chart.add_data(dataset)
    print chart.get_url()